USE Northgale
go
DECLARE @sql    nvarchar(MAX),
        @params nvarchar(4000)
        
DECLARE @cnt    int

SELECT @sql = N'SELECT @cnt = COUNT(*)
                FROM   dbo.Orders
                WHERE  CustomerID = @custid'

SELECT @params = N'@custid nchar(5),
                   @cnt    int OUTPUT'

EXEC sp_executesql @sql, @params, N'BERGS', @cnt OUTPUT

SELECT @cnt
go
-- This fails. The @cnt declared in the outer batch is not visible inside
-- the SQL string, which is a stored procedure of its own.
DECLARE @sql    nvarchar(MAX),
        @params nvarchar(4000)
        
DECLARE @cnt int

SELECT @sql = N'SELECT @cnt = COUNT(*)
                FROM   dbo.Orders
                WHERE  CustomerID = @custid'

SELECT @params = N'@custid nchar(5)'

EXEC sp_executesql @sql, @params, N'BERGS', @cnt OUTPUT

SELECT @cnt
